/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author ThanhTrung
 */
public class DBFunction {

    private String categoryName;
    private int categoryProductCount;

    public boolean checkLogin(String username, String password) throws SQLException, ClassNotFoundException {
        String sql = "Select username,password from tblUsers where username='" + username + "' and password='" + password + "'";
        DatabaseUtils db = DatabaseUtils.getInstance();
        Connection conn = db.getConnection();
        Statement stm = conn.createStatement();

        ResultSet s = stm.executeQuery(sql);
        if (s.next()) {
//          if(s.getString("username").equalsIgnoreCase(username) && s.getString("password").equals(password))
            return true;
        }

        return false;
    }

    public boolean register(String username, String password) throws SQLException, ClassNotFoundException {
        String sql = "Insert into tblUsers(username,password) values(?,?)";
        DatabaseUtils db = DatabaseUtils.getInstance();
        Connection conn = db.getConnection();
        PreparedStatement stm = conn.prepareStatement(sql);
        stm.setString(1, username);
        stm.setString(2, password);

        int i = stm.executeUpdate();
        if (i == 1) {
            return true;
        }
        return false;
    }

    public ArrayList<Product> listProducts() throws SQLException, ClassNotFoundException {
        String sql = "Select ProductID,ProductName,UnitPrice From tblProducts";
        DatabaseUtils db = DatabaseUtils.getInstance();
        Connection conn = db.getConnection();
        Statement stm = conn.createStatement();
        ResultSet rs = stm.executeQuery(sql);
        Product pro = null;
        ArrayList<Product> pros = new ArrayList<Product>();
        while (rs.next()) {
            pro = new Product();
            pro.setProductId(rs.getInt("ProductId"));
            pro.setProductName(rs.getString("ProductName"));
            pro.setUnitPrice(rs.getDouble("UnitPrice"));
            pros.add(pro);
        }
        return pros;
    }

    public ReturnedProducts getProducts(int categoryId, int pageIndex, int numRows) throws SQLException, ClassNotFoundException {
        Connection conn = null;
        CallableStatement cstm;
        ResultSet rs;
        ReturnedProducts rp;
        ArrayList<Product> pros = new ArrayList<Product>();
        System.out.println("categoryId :" + categoryId);
        System.out.println("pageIndex :" + pageIndex);
        System.out.println("numRows :" + numRows);

        try {
            conn = DatabaseUtils.getInstance().getConnection();
            cstm = conn.prepareCall("{call GetProductsByCategoryId(?,?,?,?,?)}");
            cstm.setInt(1, categoryId);
            cstm.setInt(2, pageIndex);
            cstm.setInt(3, numRows);
            cstm.registerOutParameter(4, java.sql.Types.NVARCHAR);
            cstm.registerOutParameter(5, java.sql.Types.INTEGER);
            rs = cstm.executeQuery();

            Product pro = null;

            while (rs.next()) {
                pro = new Product();
                pro.setProductId(rs.getInt("ProductId"));
                pro.setProductName(rs.getString("ProductName"));
                pro.setUnitPrice(rs.getDouble("UnitPrice"));
                pro.setCategoryId(rs.getInt("CategoryId"));
                pro.setDiscontinued(rs.getBoolean("Discontinued"));
                pro.setPicture(rs.getString("Picture"));
                pro.setQuantityInStock(rs.getInt("QuantityInStock"));
                pros.add(pro);
            }
            categoryName = cstm.getNString(4);
            categoryProductCount = cstm.getInt(5);
            System.out.println("categoryId" + categoryId);
            System.out.println("categoryName :" + categoryName);
            rp = new ReturnedProducts();
            rp.setProducts(pros);
            rp.setCategoryName(categoryName);
            rp.setCategoryProductCount(categoryProductCount);

        } catch (SQLException ex) {
            throw ex;
        } catch (ClassNotFoundException ex) {
            throw ex;
        } finally {
            if (conn != null) {
                conn = null;
            }
        }
        return rp;
    }

    public static Iterator<Category> listCategories() throws SQLException, ClassNotFoundException {
        String sql = "Select CategoryName,CategoryID from tblCategories";
        ResultSet rs = select(sql);
        List list = new ArrayList<Category>();
        Category cat = null;
        while (rs.next()) {
            cat = new Category();
            cat.setCategoryId(rs.getInt("CategoryId"));
            cat.setCategoryName(rs.getString("CategoryName"));
            list.add(cat);
        }
        return list.iterator();
    }

    public static ResultSet select(String sql) throws SQLException, ClassNotFoundException {
        DatabaseUtils db = DatabaseUtils.getInstance();
        Connection conn = db.getConnection();
        Statement stm = conn.createStatement();
        ResultSet rs = stm.executeQuery(sql);
        return rs;
    }

    public double getProductPrice(int productId) throws SQLException, ClassNotFoundException {
        String sql = "select UnitPrice from tblProducts where productId=" + productId;
        ResultSet rs = select(sql);
        rs.next();
        return rs.getDouble("UnitPrice");
    }

    public String getCategoryName() {
        return categoryName;
    }

    public int getCategoryProductCount() {
        return categoryProductCount;
    }

    public static Iterator<Product> getProduct(int productId) {
        //Delete this line
            System.out.println("Product ID : " + productId);
        ////
        Product product = null;
        ArrayList<Product> e = new ArrayList<Product>();
        String sql = "select productId,productName,unitprice, QuantityInStock,Discontinued,CategoryId,Picture from tblProducts where productId=" + productId;
        try {
            product = new Product();
            ResultSet rs = select(sql);
            if (rs.next()) {
                product.setProductId(rs.getInt("ProductID"));
                product.setProductName(rs.getString("ProductName"));
                product.setUnitPrice(rs.getDouble("UnitPrice"));
                product.setQuantityInStock(rs.getInt("QuantityInStock"));
                product.setDiscontinued(rs.getBoolean("Discontinued"));
                product.setCategoryId(rs.getInt("CategoryID"));
                product.setPicture(rs.getString("Picture"));
                e.add(product);
            }
            return e.iterator();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
        return null;
    }
}
